Execute Method Example

This example demonstrates the Execute method when run from both a QueryDef object and a Database object. The ExecuteQueryDef and PrintOutput procedures are required for this procedure to run.

Sub ExecuteX()

   Dim dbsNorthwind As Database
   Dim strSQLChange As String
   Dim strSQLRestore As String
   Dim qdfChange As QueryDef
   Dim rstEmployees As Recordset
   Dim errLoop As Error

   ' Define two SQL statements for action queries.
   strSQLChange = "UPDATE Employees SET Country = " & _
      "'United States' WHERE Country = 'USA'"
   strSQLRestore = "UPDATE Employees SET Country = " & _
      "'USA' WHERE Country = 'United States'"

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   ' Create temporary QueryDef object.
   Set qdfChange = dbsNorthwind.CreateQueryDef("", _
      strSQLChange)
   Set rstEmployees = dbsNorthwind.OpenRecordset( _
      "SELECT LastName, Country FROM Employees", _
      dbOpenForwardOnly)

   ' Print report of original data.
   Debug.Print _
      "Data in Employees table before executing the query"
   PrintOutput rstEmployees
   
   ' Run temporary QueryDef.
   ExecuteQueryDef qdfChange, rstEmployees
   
   ' Print report of new data.
   Debug.Print _
      "Data in Employees table after executing the query"
   PrintOutput rstEmployees

   ' Run action query to restore data. Trap for errors,
   ' checking the Errors collection if necessary.
   On Error GoTo Err_Execute
   dbsNorthwind.Execute strSQLRestore, dbFailOnError
   On Error GoTo 0

   ' Retrieve the current data by requerying the recordset.
   rstEmployees.Requery

   ' Print report of restored data.
   Debug.Print "Data after executing the query " & _
      "to restore the original information"
   PrintOutput rstEmployees

   rstEmployees.Close
   
   Exit Sub
   
Err_Execute:

   ' Notify user of any errors that result from
   ' executing the query.
   If DBEngine.Errors.Count > 0 Then
      For Each errLoop In DBEngine.Errors
         MsgBox "Error number: " & errLoop.Number & vbCr & _
            errLoop.Description
      Next errLoop
   End If
   
   Resume Next

End Sub

Sub ExecuteQueryDef(qdfTemp As QueryDef, _
   rstTemp As Recordset)

   Dim errLoop As Error
   
   ' Run the specified QueryDef object. Trap for errors,
   ' checking the Errors collection if necessary.
   On Error GoTo Err_Execute
   qdfTemp.Execute dbFailOnError
   On Error GoTo 0

   ' Retrieve the current data by requerying the recordset.
   rstTemp.Requery
   
   Exit Sub

Err_Execute:

   ' Notify user of any errors that result from
   ' executing the query.
   If DBEngine.Errors.Count > 0 Then
      For Each errLoop In DBEngine.Errors
         MsgBox "Error number: " & errLoop.Number & vbCr & _
            errLoop.Description
      Next errLoop
   End If
   
   Resume Next

End Sub

Sub PrintOutput(rstTemp As Recordset)

   ' Enumerate Recordset.
   Do While Not rstTemp.EOF
      Debug.Print "  " & rstTemp!LastName & _
         ", " & rstTemp!Country
      rstTemp.MoveNext
   Loop

End Sub